/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.study.dao;

import com.study.util.DButil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import com.study.dto.StudentDTO;

/**
 *
 * @author Truong Anh Tuan
 */
public class StudentDAO {

    public StudentDAO() {
    }
    Connection con = null;
    PreparedStatement pst = null;
    private final String SQL_READ_BY_ID = "SELECT * FROM students WHERE STUDENT_ID = ?";
    private final String SQL_CREATE = "INSERT INTO students(STUDENT_ID, FIRST_NAME, LAST_NAME, ADDRESS, EMAIL_ADDRESS) " +
            "VALUES(?, ?, ?, ?, ?)";
    private final String SQL_UPDATE = "UPDATE students set FIRST_NAME = ?, LAST_NAME = ?, " +
            "ADDRESS = ?, EMAIL_ADDRESS =? WHERE STUDENT_ID = ?";
    private final String SQL_DELETE = "DELETE FROM students WHERE STUDENT_ID = ?";

    public ArrayList<StudentDTO> ReadByStudentsId(String id) {
        try {
            con = DButil.GetConnection();
            pst = con.prepareStatement(SQL_READ_BY_ID);
            pst.setString(1, id);
            ResultSet rs = pst.executeQuery();
            ArrayList<StudentDTO> st = new ArrayList<StudentDTO>();
           
            if (rs.next()) {
                StudentDTO student = new StudentDTO();
                student.setStudentId(rs.getString(1));
                student.setFirstName(rs.getString(2));
                student.setLastName(rs.getString(3));
                student.setAddress(rs.getString(4));
                student.setEmail(rs.getString(5));
                st.add(student);
                return st;
            } else {
                return null;
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            DButil.closeConnection(con);
        }
    }

    public boolean Create(StudentDTO student) {
        try {
            con = DButil.GetConnection();
            pst = con.prepareStatement(SQL_CREATE);
            pst.setString(1, student.getStudentId());
            pst.setString(2, student.getFirstName());
            pst.setString(3, student.getLastName());
            pst.setString(4, student.getAddress());
            pst.setString(5, student.getEmail());
            if (pst.executeUpdate() > 0) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } finally {
            DButil.closeConnection(con);
        }
    }

    public boolean Update(StudentDTO student) {
        try {
            con = DButil.GetConnection();
            pst = con.prepareStatement(SQL_UPDATE);
            pst.setString(1, student.getFirstName());
            pst.setString(2, student.getLastName());
            pst.setString(3, student.getAddress());
            pst.setString(4, student.getEmail());
            pst.setString(5, student.getStudentId());
            if (pst.executeUpdate() > 0) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } finally {
            DButil.closeConnection(con);
        }
    }

    public boolean Delete(String id) {
        try {
            con = DButil.GetConnection();
            pst = con.prepareStatement(SQL_DELETE);
            pst.setString(1, id);
            if (pst.executeUpdate() > 0) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } finally {
            DButil.closeConnection(con);
        }
    }
}
